package persistence.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import model.entity.Item;
import model.entity.Product;
import persistence.ItemsDAO;
import common.Barcode;
import common.Result;
import common.ValueResult;
import common.util.DateUtils;

/**
 * @author Trevor Dixon, Paula Chen
 * 
 *         Contains methods for retrieving, adding, updating, and deleting items from a SQL data
 *         store.
 */
public class SQLItemsDAO implements ItemsDAO
{

  private SQLDatabase db;

  public SQLItemsDAO(SQLDatabase db)
  {
    this.db = db;
  }

  @Override
  public ValueResult<Set<Item>> getAll()
  {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Set<Item> items = new HashSet<Item>();
    try
    {
      String sql = "SELECT barcode, product_barcode, entry_date, exit_time,"
        + "container_id FROM items";
      stmt = db.getConnection().prepareStatement(sql);

      rs = stmt.executeQuery();
      while (rs.next())
      {
        ValueResult<Product> productResult = db.getProducts().getProductByBarcode(rs.getString(2));
        if (!productResult.getStatus())
        {
          if (stmt != null)
            stmt.close();
          return new ValueResult<Set<Item>>(false, "Product doesn't exist.");
        }
        Product product = productResult.getValue();
        Date expireDate = DateUtils.addMonthsToDate(rs.getDate(3), product.getShelfLife());
        Barcode itemBarcode = new Barcode(rs.getString(1));
        Item newItem = new Item(product, itemBarcode, rs.getDate(3), expireDate, null);
        newItem.setExitTime(rs.getTimestamp(4));
        newItem.setContainerId(rs.getInt(5));
        items.add(newItem);
      }
    }
    catch (SQLException e)
    {
      return new ValueResult<Set<Item>>(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (rs != null)
          rs.close();
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new ValueResult<Set<Item>>(false, "SQLException (closing).");
      }
    }
    ValueResult<Set<Item>> result = new ValueResult<Set<Item>>(true);
    result.setValue(items);
    return result;
  }

  @Override
  public ValueResult<Integer> getCurrentItemBarcodeID()
  {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Integer itemBarcodeID = null;
    try
    {
      String sql = "select value from properties where key = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setString(1, "currentItemBarcodeID");

      rs = stmt.executeQuery();
      if (rs.next())
      {
        itemBarcodeID = rs.getInt(1);
      }
      else
      {
        return new ValueResult<Integer>(false, "Product doesn't exist.");
      }
    }
    catch (SQLException e)
    {
      return new ValueResult<Integer>(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (rs != null)
          rs.close();
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new ValueResult<Integer>(false, "SQLException (closing).");
      }
    }
    ValueResult<Integer> result = new ValueResult<Integer>(true);
    result.setValue(itemBarcodeID);
    return result;
  }

  private static Timestamp timeOrNull(Date date)
  {
    if (date == null)
      return null;

    return new Timestamp(date.getTime());
  }

  @Override
  public Result add(Item item)
  {
    PreparedStatement stmt = null;
    Statement keyStmt = null;
    ResultSet keyRS = null;
    try
    {
      ValueResult<Product> productResult = db.getProducts().getProductByBarcode(
          item.getBarcode().toString());
      if (!productResult.getStatus())
      {
        return new Result(false, "Product doesn't exist.");
      }
      String sql = "INSERT INTO items (barcode, product_barcode, entry_date, exit_time,"
          + "container_id) VALUES (?, ?, ?, ?, ?)";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setString(1, item.getBarcode().getCode());
      stmt.setString(2, item.getProduct().getBarcode().getCode());
      stmt.setDate(3, new java.sql.Date(item.getEntryDate().getTime()));
      stmt.setTimestamp(4, timeOrNull(item.getExitTime()));
      stmt.setInt(5, item.getContainer().getId());

      if (stmt.executeUpdate() != 1)
      {
        return new Result(false, "Add fail.");
      }
    }
    catch (SQLException e)
    {
      return new Result(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (keyRS != null)
          keyRS.close();
        if (stmt != null)
          stmt.close();
        if (keyStmt != null)
          keyStmt.close();
      }
      catch (SQLException e)
      {
        return new Result(false, "SQLException (closing).");
      }
    }
    return new Result(true);
  }

  @Override
  public Result update(Item item)
  {
    PreparedStatement stmt = null;
    try
    {
      String sql = "update items "
          + "set product_barcode = ?, entry_date = ?, exit_time = ?, container_id = ? "
          + "where barcode = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setString(1, item.getProduct().getBarcode().toString());
      stmt.setDate(2, new java.sql.Date(item.getEntryDate().getTime()));
      stmt.setTimestamp(3, timeOrNull(item.getExitTime()));
      stmt.setInt(4, item.getContainer().getId());
      stmt.setString(5, item.getBarcode().toString());

      if (stmt.executeUpdate() != 1)
      {
        return new Result(false, "Updating item failed.");
      }
    }
    catch (SQLException e)
    {
      return new Result(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new Result(false, "SQLException (closing).");
      }
    }
    return new Result(true);
  }

  @Override
  public Result delete(Item item)
  {
    PreparedStatement stmt = null;
    try
    {
      String sql = "delete from items where barcode = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setString(1, item.getBarcode().toString());

      if (stmt.executeUpdate() != 1)
      {
        return new Result(false, "Delete fail.");
      }
    }
    catch (SQLException e)
    {
      return new Result(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new Result(false, "SQLException (closing).");
      }
    }
    return new Result(true);
  }

  @Override
  public Result save(java.util.Set<Item> items)
  {
    return new Result(false, "Method not supported for this persistence mode.");
  }

  @Override
  public Result saveCurrentItemBarcodeID(int id)
  {
    PreparedStatement stmt = null;
    try
    {
      String sql = "update properties set value = ? where key = ?";
      stmt = db.getConnection().prepareStatement(sql);
      stmt.setInt(1, id);
      stmt.setString(2, "currentItemBarcodeID");

      if (stmt.executeUpdate() != 1)
      {
        return new Result(false, "Saving currentItemBarcodeID failed.");
      }
    }
    catch (SQLException e)
    {
      return new Result(false, "SQLException.");
    }
    finally
    {
      try
      {
        if (stmt != null)
          stmt.close();
      }
      catch (SQLException e)
      {
        return new Result(false, "SQLException (closing).");
      }
    }
    return new Result(true);
  }
}
